package dbms;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import core.Configuration;

public class DBCheckAndConfigurationFunctions {

	/*
	 * SERVER CONFIGURATION PARAMETERS:
	 * 0 - USERID 
	 * 1 - PASSWORD
	 * 2 - MAIN PATH
	 * 3 - CONNECTION PORT
	 * 4 - FILE TRANSFER PORT
	 * 5 - TEMP DIR
	 */
	
	public static boolean isDBConfigured()
	{
		if(DBCheckAndConfigurationFunctions.tableExists(DBTables.CONFIG))
			if(DBCheckAndConfigurationFunctions.tableExists(DBTables.UTENTE))
				if(DBCheckAndConfigurationFunctions.tableExists(DBTables.PROGRAM))
					if(DBCheckAndConfigurationFunctions.tableExists(DBTables.PROG_UTENTE))

						if(DBCheckAndConfigurationFunctions.isConfigTableSet())
							return true;
		return false;
	}
	
	public static boolean isConfigTableSet()
	{
		try {
			
			ResultSet res;
			Statement st = DBConnection.getIstance().createStatement();
			
			res = st.executeQuery("SELECT * FROM " + DBTables.CONFIG + " WHERE id=0");
			if(!res.next()) return false;
			
			res = st.executeQuery("SELECT * FROM " + DBTables.CONFIG + " WHERE id=1");
			if(!res.next()) return false;

			res = st.executeQuery("SELECT * FROM " + DBTables.CONFIG + " WHERE id=2");
			if(!res.next()) return false;
			
			res = st.executeQuery("SELECT * FROM " + DBTables.CONFIG + " WHERE id=3");
			if(!res.next()) return false;
			
			res = st.executeQuery("SELECT * FROM " + DBTables.CONFIG + " WHERE id=4");
			if(!res.next()) return false;
			
			res = st.executeQuery("SELECT * FROM " + DBTables.CONFIG + " WHERE id=5");
			if(!res.next()) return false;
			
			return true;
			
		} catch (SQLException e) {
			return false;
		}		
	}
	
	public static boolean tableExists(String tableName)
	{	
		try {
			DatabaseMetaData dbm = DBConnection.getIstance().getMetaData();
			ResultSet rs = dbm.getTables(null, null, tableName.toUpperCase(), null);
			
			return rs.next();			
		
		} catch (SQLException e) {
			System.out.println("QUI 1");
			return false;
		}
	}
	
	public static boolean setServerUser(String userName, String password)
	{
		try {
			String sql;
			PreparedStatement ps;
			
			DBConnection.getIstance().setAutoCommit(false);

			if(!Configuration.isConfigured())
			{	
				sql = "INSERT INTO " + DBTables.CONFIG + " VALUES (0, ?)";
				ps = DBConnection.getIstance().prepareStatement(sql);
				ps.setString(1, userName);
				ps.executeUpdate();
				
				sql = "INSERT INTO " + DBTables.CONFIG + " VALUES (1, ?)";
				ps = DBConnection.getIstance().prepareStatement(sql);
				ps.setString(1, password);
				ps.executeUpdate();
			} else
			{
				sql = "UPDATE " + DBTables.CONFIG + " SET value=? WHERE id=0";
				ps = DBConnection.getIstance().prepareStatement(sql);
				ps.setString(1, userName);
				ps.executeUpdate();
				
				sql = "UPDATE " + DBTables.CONFIG + " SET value=? WHERE id=1";
				ps = DBConnection.getIstance().prepareStatement(sql);
				ps.setString(1, password);
				ps.executeUpdate();				
			}
			
			DBConnection.getIstance().commit();
			DBConnection.getIstance().setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			System.out.println("QUI 2");
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean setServerMainPath(String path)
	{
		try {
			DBConnection.getIstance().setAutoCommit(false);

			String sql;
			PreparedStatement ps;
			
			if(!Configuration.isConfigured())
				sql = "INSERT INTO " + DBTables.CONFIG + " VALUES (2, ?)";
			else
				sql = "UPDATE " + DBTables.CONFIG + " SET value=? WHERE id=2";
				
			ps = DBConnection.getIstance().prepareStatement(sql);
			ps.setString(1, path);
			ps.executeUpdate();

			DBConnection.getIstance().commit();
			DBConnection.getIstance().setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			System.out.println("QUI 3");
			return false;
		}
	}
	
	public static boolean setServerConnectionPort(int port)
	{
		try {
			DBConnection.getIstance().setAutoCommit(false);

			String sql;
			PreparedStatement ps;
			
			if(!Configuration.isConfigured())
				sql = "INSERT INTO " + DBTables.CONFIG + " VALUES (3, ?)";
			else
				sql = "UPDATE " + DBTables.CONFIG + " SET value=? WHERE id=3";
		
			ps = DBConnection.getIstance().prepareStatement(sql);
			ps.setInt(1, port);
			ps.executeUpdate();
		
			DBConnection.getIstance().commit();
			DBConnection.getIstance().setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			System.out.println("QUI 3 e 1/2");
			e.printStackTrace();
			return false;
		}
	}
	
	public static boolean setFileTransferConnectionPort(int port)
	{
		try {
			DBConnection.getIstance().setAutoCommit(false);

			String sql;
			PreparedStatement ps;
			
			if(!Configuration.isConfigured())
				sql = "INSERT INTO " + DBTables.CONFIG + " VALUES (4, ?)";
			else
				sql = "UPDATE " + DBTables.CONFIG + " SET value=? WHERE id=4";
				
			ps = DBConnection.getIstance().prepareStatement(sql);
			ps.setInt(1, port);
			ps.executeUpdate();
			
			DBConnection.getIstance().commit();
			DBConnection.getIstance().setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			System.out.println("QUI 4");
			return false;
		}
	}
	
	public static boolean setServerTempDir(String tempDir)
	{
		try {
			DBConnection.getIstance().setAutoCommit(false);

			String sql;
			PreparedStatement ps;
			
			if(!Configuration.isConfigured())
				sql = "INSERT INTO " + DBTables.CONFIG + " VALUES (5, ?)";
			else
				sql = "UPDATE " + DBTables.CONFIG + " SET value=? WHERE id=5";
			
			ps = DBConnection.getIstance().prepareStatement(sql);
			ps.setString(1, tempDir);
			ps.executeUpdate();
						
			DBConnection.getIstance().commit();
			DBConnection.getIstance().setAutoCommit(true);
			
			return true;
		} catch (SQLException e) {
			System.out.println("QUI 5");
			return false;
		}
	}
}
